import random

from PIL.XVThumbImagePlugin import r
from flask import Blueprint, jsonify, redirect
from kombu.utils import json

from db import db
import datetime
from flask import current_app as app,request
user_blue = Blueprint('user',__name__)

@user_blue.route("/test")
def test():
    str = datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d %H:%M:%S')+"   "+request.path+"   error"
    app.logger.error(str)
    app.logger.info('info log')
    app.logger.warning('warning log')

    sql = "insert into user values(0,'aa22222','abc')"
    db.update(sql)
    db.commit()
    sql1 = "select * from user"
    res = db.find_all(sql1)
    print(res)
    return 'ok234234'

# 获取标签全部数据
def gettajes():
    sql = 'select * from label'
    res = db.findAll(sql)
    return res

# 获取课程全部数据s
def getcourses():
    sql = 'select * from courses'
    res = db.findAll(sql)
    return res

# 获取类别全部数据
def categorie():
    sql = 'select * from categorie'
    res = db.findAll(sql)
    return res

def home1():
    list1 = gettajes()
    list2 = getcourses()
    list3 = categorie()
    return jsonify({'code':200,'list1':list1,'list2':list2,'list3':list3})

@user_blue.route('/home')
def home():
    list1 = gettajes()
    list3 = categorie()
# 获取当前页面
    page = request.args.get("page")
    # 获取查询条件
    typeid = request.args.get("type")
    tagid = request.args.get("tagid")
    online = request.args.get("online")
    # 排序条件
    orderbyid = request.args.get("orderbyid")


    where = ""
    if int(typeid) > 0:
        where = where + " and c.type=" + typeid

    if int(online) > 0:
        where = where + " and c.online=" + online

    if int(tagid) > 0:
        tsql = "select count(c.id) as tcount from courses as c inner join courses_label as cl on cl.coursesid=c.id  where cl.labelid=" + tagid
        sql = "select * from courses as c inner join courses_label as cl on cl.coursesid=c.id  where cl.labelid=" + tagid
    else:
        sql = "select * from courses as c  where 1=1"
        tsql = "select count(c.id) as  tcount   from courses as c  where 1=1"

    if where:
        sql = sql + " " + where
        tsql = tsql + " " + where

    if int(orderbyid) > 0:
        if int(orderbyid) == 1:
            sql = sql + " order by c.id desc"
        else:
            sql = sql + " order by c.click_num desc"

    # 查询总共有多少条
    res = db.findone(tsql)
    total = res['tcount']
    page_size = 2
    start = (int(page) - 1) * page_size
    sql = sql + " limit %d,%d" % (start, page_size)
    courselist = db.findAll(sql)
    print(sql)
    return jsonify({"code": 200, "tlist": list3, 'taglist': list1, 'clist': courselist, "total": total,
                    "page_size": page_size})

from utils.comm import get_ali_object
import time
@user_blue.route("/getviplist")
def getviplist():
    sql ="select * from vip_setting"
    res = db.findAll(sql)
    list =[{"id":i['id'],"name":i["name"],"type":i['vtype'],"price":float(i['price'])} for i in res]
    return jsonify({"code":200,"list":list})


@user_blue.route('/setvip', methods=['POST'])
def setvipdui():
    data = json.loads(request.data)
    userid = data['userid']
    types = data['type']
    # 根据类型获取价格，根据类型计算到期时间
    sql = "select * from vip_setting where  vtype=%d" % (int(types))
    res = db.findone(sql)
    # 写入vip订单表
    price = float(res['price'])
    # 获取当前时间转成字符串
    stime = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S")
    days1 = 365
    if int(types) == 1:
        days1 = 30
    # 计算一年后或者一个月后日期
    etime = datetime.datetime.now() + datetime.timedelta(days=days1)
    etime = datetime.datetime.strftime(etime, "%Y-%m-%d %H:%M:%S")
    sql1 = "insert into vip_order(userid,type,price,create_time,pay_status,expires_time) values(%d,%d,%f,'%s',1,'%s')" % (
        int(userid), int(types), price, stime, etime)
    id = db.add(sql1)
    db.commit()
    # 支付
    alipay = get_ali_object()
    # 生成支付的url
    query_params = alipay.direct_pay(
        subject=str(userid) + "购买vip",  # 商品简单描述
        out_trade_no=id,  # 用户购买的商品订单号（每次不一样） 20180301073422891
        total_amount=price,  # 交易金额(单位: 元 保留俩位小数)
    )
    pay_url = "https://openapi.alipaydev.com/gateway.do?{0}".format(query_params)  # 支付宝网关地址（沙箱应用）
    # 把订单号加入延时队列
    # pkey = 'orderpayfail'
    # r.sortedset_add(pkey, id,int(time.time()))

    return jsonify({'code': 200, 'url': pay_url})
@user_blue.route("/testalipay")
def testalipay():
    alipay = get_ali_object()
    # 生成支付的url
    query_params = alipay.direct_pay(
    subject="test",  # 商品简单描述
    out_trade_no="asfasf"+str(time.time()),  # 用户购买的商品订单号（每次不一样） 20180301073422891
    total_amount=100,  # 交易金额(单位: 元 保留俩位小数)
    )
    pay_url = "https://openapi.alipaydev.com/gateway.do?{0}".format(query_params)  # 支付宝网关地址（沙箱应用）
    return redirect(pay_url)

def updateorders(out_trade_no,trade_no):
    # 加事务处理
    # 更新订单状态
    try:
        stime = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S")
        sql = "update vip_order set pay_status=2,pay_time='%s',transaction_no='%s' where id=%d"%(stime,trade_no,int(out_trade_no))
        db.add(sql)
        # 更新会员状态
        # 查询订单对应的用户
        sql1 = "select userid from vip_order where id=%d"%int(out_trade_no)
        user = db.findone(sql1)
        usql = "update users set type=2 where id=%d"%(int(user['userid']))
        db.add(usql)
        #从队列中删除
        r.sortedset_delete('orderpayfail')
        db.commit()
    except:
        db.rollback()
@user_blue.route("/callback")
def callback():
    trade_no = request.args.get('trade_no') #流水号
    out_trade_no = request.args.get('out_trade_no') #订单号
    updateorders(out_trade_no, trade_no)
    # 跳转到个人中心页面 我的订单页面
    return redirect("http://localhost:8081/#/vip")

@user_blue.route("/detail")
def detail():
    #获取课程id
    id = request.args.get('id')
    #从缓存中查询课程及章节信息
    key = "course"+str(id)
    skey = "section"+str(id)
    course = r.str_get(key)
    if course:
        print("缓存")
        section = r.str_get(skey)
        return jsonify({"code":200,'clist':json.loads(course),'slist':json.loads(section)})
    #如果存在直接返回，如果不存在查询数据库
    else:
        print("查询")
        sql = "select c.id,s.id as sid,c.name,c.description,c.collent_sum,s.title,s.url from courses as c inner join sections as s on c.id=s.coursesid where c.id=%d order by s.sort asc"%(int(id))
        print(sql)
        res = db.findAll(sql)
        section=[]
        #获取课程下所有的章节
        for i in res:
            section.append({"id":i['sid'],"title":i['title'],"url":i['url'],"courseid":i['id']})
        #获取课程信息
        course = {"id":res[0]['id'],"name":res[0]['name'],'description':res[0]['description'],'collent_sum':res[0]['collent_sum']}
        #数据解析存入redis
        r.str_add(key,json.dumps(course))
        r.str_add(skey,json.dumps(section))
        return jsonify({"code":200,'clist':course,'slist':section})
@user_blue.route('/getVideo')
def getVideo():
    # 获取用户id和章节id
    courseid = request.args.get('courseid')
    userid = request.args.get('userid')
    id = request.args.get('id')
    # 通过用户表查询你用户级别
    sql = "select type from users where id=%d"%(int(userid))
    user = db.findone(sql)
    # 通过假恶丑id查询课程类型
    key = "course" + str(courseid)
    course = r.str_get(key)
    if course:
        course = json.loads(course)
        ctype = course['type']

        if int(ctype) == 2:
            # 会员可看

            if int(user['type']) != 2:
                return jsonify({'code':1011,'msg':'没有权限观看'})

         # 课程type=3购买课程
        if int(ctype) == 3:
            bsql = "select * from user_courses where userid=%d and courseid=%d"%(int(userid),int(courseid))
            bres = db.findone(bsql)
            if not bres:
                return jsonify({'code':10012,'msg':'此课程需要单独购买'})
        # 返回此章节的url

        skey = "section" + str(courseid)
        sections = json.loads(r.str_get(skey))
        for i in sections:
            if int(i['id']) == int(id): #redis缓存中的id和前端章节id
                url = i['url']
                print(url)
                return jsonify({'code':200,'url':url})


    else:
        return jsonify({'code':10010,})
     # 用户权限和课程权限的判断
     # 返回此章节的id

#加入购物车
@user_blue.route("/addcart",methods=['POST'])
def addcart():
    #获取参数
    data = json.loads(request.data)
    userid = data['userid']
    goodsid = data['goodsid']
    #调用redis add hash进行添加
    pkey = "mycart"+str(userid)
    r.hash_add(pkey,goodsid,1)
    return jsonify({"code":200,"msg":"加入购物车成功"})

# 我的购物车
@user_blue.route("/getcourses")
def getcart():
    userid = request.args.get("userid")
    pkey = "mycart"+str(userid)
    res = r.hash_getall(pkey)
    for i,j in res.items():
        goodsid = i.decode()
        sql = "select id,name,price from courses where id= %d"%int(goodsid)
        res = db.findone(sql)
    return jsonify({"code": 200, "msg":"ok","list": res})



@user_blue.route('/pay',methods=['POST'])
def addcarts():
    data = json.loads(request.data)
    userid = int(data['userid'])
    courseid = int(data['courseid'])
    sum = 0
    # sql = "select * from user_courses where userid=%d and courseid=%d"%(userid,courseid)
    # # pkey = 'mycart'+str(userid)
    # res = db.findone(sql)
    # if res:
    #     return jsonify({'code':400,'msg':'此书已经购买'})
    try:
        sql = "select * from courses where id= %d" % courseid
        res = db.findone(sql)
        sum += float(res["price"])
        # 添加到用户购买表
        usql = "insert into user_courses(userid, courseid) values (%d,%d)"%(userid,courseid)
        db.add(usql)
        # 查询课程的价格，图片
        csql = "select * from courses where id=%d"%courseid
        course = db.findone(csql)
        name = course['name']
        img = course['img']

        number = str(int(time.time()))+str(userid)+str(random.randint(100000,999999))
        stime = datetime.datetime.strftime(datetime.datetime.now(),"%Y-%m-%d %H:%M:%S")
        ptime = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S")
        status = 1
        dsql = "insert into order_desc(number, coursesid, name, price, img, usersid) values ('%s',%d,'%s','%f','%s',%d)" % (number, courseid, name, sum, img, userid)
        db.add(dsql)
        osql = "insert into order1(price,create_time,status,pay_time,serial_number,usersid,number) values ('%f','%s','%s','%s',%d,%d,'%s')"%(sum,stime,status,ptime,1,userid,number)
        db.add(osql)
        db.commit()
    except:
        db.rollback()
    # 支付
    alipay = get_ali_object()
    query_params = alipay.direct_pay(
        subject=str(userid) + "购买课程",  # 商品简单描述
        out_trade_no=number,  # 用户购买的商品订单号（每次不一样） 20180301073422891
        total_amount=sum,  # 交易金额(单位: 元 保留俩位小数)
    )
    pay_url = "https://openapi.alipaydev.com/gateway.do?{0}".format(query_params)  # 支付宝网关地址（沙箱应用）
    # 把订单号写入延时队列当中
    # pkey = 'orderpayfailorder1'
    # r.sortedset_add(pkey, number, int(time.time()))
    return jsonify({
        'code': 200,
        'url': pay_url
    })


@user_blue.route("/callback")
def callback():
    try:
        sql = "update order1 set status=2"
        db.add(sql)
        db.commit()
    except:
        db.rollback()
    # 跳转到个人中心特免
    return redirect('http://localhost:8080/#/courses')

# #训练营商品表
# create table commodity(
# id int primary key auto_increment,
# name varchar(256),
# price decimal(5,2),
# img varchar(256),
# descs varchar(256),
# pop_up  int,
# create_time datetime,
# inventory int,
# lock_store int
# );
# insert into commodity(id, name, price, img, descs, pop_up, create_time, inventory, lock_store) values
# #训练营订单表
# create table commodity_order(
#     id int primary key auto_increment,
#     number varchar(256),
#     count int,
#     count_price varchar(256),
#     create_times datetime,
#     status varchar(256),
#     pay_times datetime,
#     serial_numbers varchar(256),
#     goodid int,
#     goods_name varchar(256),
#     goods_price decimal(5,2),
#     goods_img varchar(256),
#     usersid int
#     );